﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class MSESystemInstance
    {
        public MSESystemInstance()
        {

        }

        public static Guid CreateSystemInstance(DTO.SystemInstance systemInstance, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSystemInstance]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter SystemIDParam = new SqlParameter("@SystemID", SqlDbType.UniqueIdentifier);
                    SqlParameter OwnerNameParam = new SqlParameter("@OwnerName", SqlDbType.NVarChar, 200);
                    SqlParameter OwnerContactParam = new SqlParameter("@OwnerContact", SqlDbType.NVarChar, 200);
                    SqlParameter URIParam = new SqlParameter("@URI", SqlDbType.NVarChar, 500);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    if (systemInstance.SystemID != null && systemInstance.SystemID != Guid.Empty)
                        SystemIDParam.Value = systemInstance.SystemID;
                    NameParam.Value = systemInstance.Name;
                    PortParam.Value = systemInstance.Port;
                    if (systemInstance.OwnerName != null)
                    {
                        OwnerNameParam.Value = systemInstance.OwnerName;
                    }
                    else
                    {
                        OwnerNameParam.Value = DBNull.Value;
                    }
                    if (systemInstance.OwnerContact != null)
                    {
                        OwnerContactParam.Value = systemInstance.OwnerContact;
                    }
                    else
                    {
                        OwnerContactParam.Value = DBNull.Value;
                    }
                    if (systemInstance.Description != null)
                    {
                        DescriptionParam.Value = systemInstance.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }
                    URIParam.Value = systemInstance.URI;
                    if (systemInstance.Policy != null)
                    {
                        PolicyIDParam.Value = systemInstance.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(SystemIDParam);
                    cmd.Parameters.Add(OwnerNameParam);
                    cmd.Parameters.Add(OwnerContactParam);
                    cmd.Parameters.Add(URIParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.SystemInstance UpdateSystemInstance(DTO.SystemInstance systemInstance, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateSystemInstance]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter SystemIDParam = new SqlParameter("@SystemID", SqlDbType.UniqueIdentifier);
                    SqlParameter OwnerNameParam = new SqlParameter("@OwnerName", SqlDbType.NVarChar, 200);
                    SqlParameter OwnerContactParam = new SqlParameter("@OwnerContact", SqlDbType.NChar, 200);
                    SqlParameter URIParam = new SqlParameter("@URI", SqlDbType.NVarChar, 500);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = systemInstance.ID;
                    if (systemInstance.SystemID != null && systemInstance.SystemID != Guid.Empty)
                        SystemIDParam.Value = systemInstance.SystemID;
                    NameParam.Value = systemInstance.Name;
                    PortParam.Value = systemInstance.Port;
                    if (systemInstance.OwnerName != null)
                    {
                        OwnerNameParam.Value = systemInstance.OwnerName;
                    }
                    else
                    {
                        OwnerNameParam.Value = DBNull.Value;
                    }
                    if (systemInstance.OwnerContact != null)
                    {
                        OwnerContactParam.Value = systemInstance.OwnerContact;
                    }
                    else
                    {
                        OwnerContactParam.Value = DBNull.Value;
                    }
                    if (systemInstance.Description != null)
                    {
                        DescriptionParam.Value = systemInstance.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }
                    URIParam.Value = systemInstance.URI;
                    if (systemInstance.Policy != null)
                    {
                        PolicyIDParam.Value = systemInstance.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    //


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(SystemIDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(OwnerNameParam);
                    cmd.Parameters.Add(OwnerContactParam);
                    cmd.Parameters.Add(URIParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return systemInstance;
        }

        public static void DeleteSystemInstance(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSystemInstance]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static DTO.SystemInstance GetSystemInstance(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SystemInstance systemInstance = new DTO.SystemInstance();
            const string sprocname = "[ServiceEngine].[GetSystemInstance]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    //SELECT	N.[EntityID] as SystemInstanceID, N.[EntityName] as [Name], 
                    //SI.SystemID, SI.OwnerName, SI.URI, N.EntityDescription as Description, S.OwnerContact, S.PolicyID

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            systemInstance.ID = reader.GetGuid(0);
                            systemInstance.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                systemInstance.SystemID = Guid.Empty;
                            }
                            else
                            {
                                systemInstance.SystemID = reader.GetGuid(2);
                            }

                            if (reader.IsDBNull(3))
                            {
                                systemInstance.OwnerName = null;
                            }
                            else
                            {
                                systemInstance.OwnerName = reader.GetString(3);
                            }
                            if (reader.IsDBNull(4))
                            {
                                systemInstance.URI = null;
                            }
                            else
                            {
                                systemInstance.URI = reader.GetString(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                systemInstance.Description = null;
                            }
                            else
                            {
                                systemInstance.Description = reader.GetString(5);
                            }
                            if (reader.IsDBNull(6))
                            {
                                systemInstance.OwnerContact = null;
                            }
                            else
                            {
                                systemInstance.OwnerContact = reader.GetString(6);
                            }

                            systemInstance.Policy = reader.IsDBNull(7) ? null : CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(7), txn, conn);

                            if (reader.IsDBNull(8))
                            {
                                systemInstance.Port = 0;
                            }
                            else
                            {
                                systemInstance.Port = reader.GetInt32(8);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return systemInstance;

        }

        public static DTO.SystemInstanceList ListSystemInstances(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SystemInstanceList list = new DTO.SystemInstanceList();
            const string sprocname = "[ServiceEngine].[ListSystemInstances]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.SystemInstances = new List<DTO.SystemInstanceListElement>();
                            while (reader.Read())
                            {
                                DTO.SystemInstanceListElement element = new DTO.SystemInstanceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.SystemInstances.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.SystemInstanceList ListSystemInstancesForSystem(Guid systemID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SystemInstanceList list = new DTO.SystemInstanceList();
            const string sprocname = "[ServiceEngine].[ListSystemInstancesForSystem]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SystemID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = systemID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.SystemInstances = new List<DTO.SystemInstanceListElement>();
                            while (reader.Read())
                            {
                                DTO.SystemInstanceListElement element = new DTO.SystemInstanceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.SystemInstances.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.SystemInstanceListElement> ListSystemInstancesByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.SystemInstanceListElement> list = new List<DTO.SystemInstanceListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.SystemInstanceListElement>();
                            while (reader.Read())
                            {
                                DTO.SystemInstanceListElement element = new DTO.SystemInstanceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

    }
}
